﻿using System; 
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Matrix.Common.DBUtility;
using Matrix.MaKaYunShang.Model;
using Matrix.MaKaYunShang.Model.Dto;
using Matrix.MaKaYunShang.Model.Enum;

namespace Matrix.MaKaYunShang.DAL
{
	public partial class McCashWithdrawApplicationDAL : BaseDAL<McCashWithdrawApplicationDAL>
	{
        #region 默认方法
        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(McCashWithdrawApplication model)
        {
            var strSql = new StringBuilder();
            strSql.Append("insert into McCashWithdrawApplication(");
            strSql.Append("PayTime,PayOptAccountId,Longitude,Remark,Latitude,IpAddress,DealStatus,IsDelete,CashValue,ApplyAccountId,ApplyTime,ShopId,BankAccount,BankName,BankOfDeposit,BankAccountName");
            strSql.Append(") values (");
            strSql.Append("@PayTime,@PayOptAccountId,@Longitude,@Remark,@Latitude,@IpAddress,@DealStatus,@IsDelete,@CashValue,@ApplyAccountId,@ApplyTime,@ShopId,@BankAccount,@BankName,@BankOfDeposit,@BankAccountName");
            strSql.Append(") ");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
			            new SqlParameter("@PayTime", SqlDbType.DateTime) ,            
                        new SqlParameter("@PayOptAccountId", SqlDbType.Int,4) ,            
                        new SqlParameter("@Longitude", SqlDbType.NVarChar,16) ,            
                        new SqlParameter("@Remark", SqlDbType.NVarChar,500) ,            
                        new SqlParameter("@Latitude", SqlDbType.NVarChar,16) ,            
                        new SqlParameter("@IpAddress", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@DealStatus", SqlDbType.Int,4) ,            
                        new SqlParameter("@IsDelete", SqlDbType.Bit,1) ,            
                        new SqlParameter("@CashValue", SqlDbType.Int,4) ,            
                        new SqlParameter("@ApplyAccountId", SqlDbType.Int,4) ,            
                        new SqlParameter("@ApplyTime", SqlDbType.DateTime) ,            
                        new SqlParameter("@ShopId", SqlDbType.Int,4) ,            
                        new SqlParameter("@BankAccount", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@BankName", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@BankOfDeposit", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@BankAccountName", SqlDbType.NVarChar,50)             
              
            };

            parameters[0].Value = model.PayTime;
            parameters[1].Value = model.PayOptAccountId;
            parameters[2].Value = model.Longitude;
            parameters[3].Value = model.Remark;
            parameters[4].Value = model.Latitude;
            parameters[5].Value = model.IpAddress;
            parameters[6].Value = model.DealStatus;
            parameters[7].Value = model.IsDelete;
            parameters[8].Value = model.CashValue;
            parameters[9].Value = model.ApplyAccountId;
            parameters[10].Value = model.ApplyTime;
            parameters[11].Value = model.ShopId;
            parameters[12].Value = model.BankAccount;
            parameters[13].Value = model.BankName;
            parameters[14].Value = model.BankOfDeposit;
            parameters[15].Value = model.BankAccountName; var obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            return obj == null ? 0 : Convert.ToInt32(obj);
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(McCashWithdrawApplication model)
        {
            var strSql = new StringBuilder();
            strSql.Append("update McCashWithdrawApplication set ");

            strSql.Append(" PayTime = @PayTime , ");
            strSql.Append(" PayOptAccountId = @PayOptAccountId , ");
            strSql.Append(" Longitude = @Longitude , ");
            strSql.Append(" Remark = @Remark , ");
            strSql.Append(" Latitude = @Latitude , ");
            strSql.Append(" IpAddress = @IpAddress , ");
            strSql.Append(" DealStatus = @DealStatus , ");
            strSql.Append(" IsDelete = @IsDelete , ");
            strSql.Append(" CashValue = @CashValue , ");
            strSql.Append(" ApplyAccountId = @ApplyAccountId , ");
            strSql.Append(" ApplyTime = @ApplyTime , ");
            strSql.Append(" ShopId = @ShopId , ");
            strSql.Append(" BankAccount = @BankAccount , ");
            strSql.Append(" BankName = @BankName , ");
            strSql.Append(" BankOfDeposit = @BankOfDeposit , ");
            strSql.Append(" BankAccountName = @BankAccountName  ");
            strSql.Append(" where Id=@Id ");

            SqlParameter[] parameters = {
			            new SqlParameter("@Id", SqlDbType.Int,4) ,            
                        new SqlParameter("@PayTime", SqlDbType.DateTime) ,            
                        new SqlParameter("@PayOptAccountId", SqlDbType.Int,4) ,            
                        new SqlParameter("@Longitude", SqlDbType.NVarChar,16) ,            
                        new SqlParameter("@Remark", SqlDbType.NVarChar,500) ,            
                        new SqlParameter("@Latitude", SqlDbType.NVarChar,16) ,            
                        new SqlParameter("@IpAddress", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@DealStatus", SqlDbType.Int,4) ,            
                        new SqlParameter("@IsDelete", SqlDbType.Bit,1) ,            
                        new SqlParameter("@CashValue", SqlDbType.Int,4) ,            
                        new SqlParameter("@ApplyAccountId", SqlDbType.Int,4) ,            
                        new SqlParameter("@ApplyTime", SqlDbType.DateTime) ,            
                        new SqlParameter("@ShopId", SqlDbType.Int,4) ,            
                        new SqlParameter("@BankAccount", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@BankName", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@BankOfDeposit", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@BankAccountName", SqlDbType.NVarChar,50)             
              
            };

            parameters[0].Value = model.Id;
            parameters[1].Value = model.PayTime;
            parameters[2].Value = model.PayOptAccountId;
            parameters[3].Value = model.Longitude;
            parameters[4].Value = model.Remark;
            parameters[5].Value = model.Latitude;
            parameters[6].Value = model.IpAddress;
            parameters[7].Value = model.DealStatus;
            parameters[8].Value = model.IsDelete;
            parameters[9].Value = model.CashValue;
            parameters[10].Value = model.ApplyAccountId;
            parameters[11].Value = model.ApplyTime;
            parameters[12].Value = model.ShopId;
            parameters[13].Value = model.BankAccount;
            parameters[14].Value = model.BankName;
            parameters[15].Value = model.BankOfDeposit;
            parameters[16].Value = model.BankAccountName; return DbHelperSQL.ExecuteSql(strSql.ToString(), parameters) > 0;
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int Id)
        {
            var strSql = new StringBuilder();
            strSql.Append("update McCashWithdrawApplication set IsDelete=1 ");
            strSql.Append(" where Id=@Id");
            SqlParameter[] parameters = {
					new SqlParameter("@Id", SqlDbType.Int,4)
			};
            parameters[0].Value = Id;

            return DbHelperSQL.ExecuteSql(strSql.ToString(), parameters) > 0;
        }

        /// <summary>
        /// 批量删除一批数据
        /// </summary>
        public bool DeleteList(string Idlist)
        {
            var strSql = new StringBuilder();
            strSql.Append("update McCashWithdrawApplication set IsDelete=1 ");
            strSql.Append(" where Id in (" + Idlist + ")  ");
            return DbHelperSQL.ExecuteSql(strSql.ToString()) > 0;
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public DataSet GetModel(int Id)
        {
            var strSql = new StringBuilder();
            strSql.Append("select Id, PayTime, PayOptAccountId, Longitude, Remark, Latitude, IpAddress, DealStatus, IsDelete, CashValue, ApplyAccountId, ApplyTime, ShopId, BankAccount, BankName, BankOfDeposit, BankAccountName  ");
            strSql.Append("  from McCashWithdrawApplication ");
            strSql.Append(" where Id=@Id  and IsDelete=0");
            SqlParameter[] parameters = {
					new SqlParameter("@Id", SqlDbType.Int,4)
			};
            parameters[0].Value = Id;

            var ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            return ds.Tables[0].Rows.Count > 0 ? ds : null;
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            var strSql = new StringBuilder();
            strSql.Append("select * ");
            strSql.Append(" FROM McCashWithdrawApplication where IsDelete=0 ");
            if (!string.IsNullOrEmpty(strWhere))
            {
                strSql.Append(" and " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }
        #endregion
		#region 扩展方法

        /// <summary>
        /// 获取店铺历史总提现金额
        /// </summary>
        /// <param name="shopId"></param>
        /// <returns></returns>
        public List<McCashWithdrawApplication> GetTotalWithdrawCashByShopId(int shopId)
        {
            var sbSql = new StringBuilder();
            sbSql.Append("SELECT mw.* ");
            sbSql.Append("From McCashWithdrawApplication mw ");
            sbSql.Append("WHERE mw.IsDelete=0 AND mw.ShopId=@ShopId and (DealStatus=@DealStatus or DealStatus=@DealStatus2)");

            SqlParameter[] parameters = {
					new SqlParameter("@ShopId", SqlDbType.Int,4),
                    new SqlParameter("@DealStatus", SqlDbType.Int,4),
                    new SqlParameter("@DealStatus2", SqlDbType.Int,4)
			};
            parameters[0].Value = shopId;
            parameters[1].Value = (int)CommonEnum.McWithdrawDealStatus.Success;
            parameters[2].Value = (int)CommonEnum.McWithdrawDealStatus.Pending;
            var obj = DbHelperSQL.ExecuteSqlList<McCashWithdrawApplication>(sbSql.ToString(), parameters);
            return obj == null  || obj.Count == 0 ? null : obj.ToList();
        }


        /// <summary>
        /// 获取分页数据
        /// </summary>
        /// <returns></returns>
        public List<ViewMcCashWithdrawApplicationDto> GetPagingList(int pageIndex, int pageSize, string searchKeyWord, string colOrder, int dealStatus,int shopId, ref int totalCount)
        {
            string strWhere = " mwa.IsDelete = 0 AND ss.IsDelete=0 ";

            if (dealStatus > -1)
            {
                strWhere += " and mwa.dealStatus=" + dealStatus;
            }
            if (shopId > 0)
            {
                strWhere += " and mwa.shopId=" + shopId;
            }

            var strSelectColumn2 = "mwa.*, ss.Name as ShopName";
            var strTableName = "McCashWithdrawApplication AS mwa INNER JOIN Shops ss ON ss.Id=mwa.ShopId";

            return GetSimpleListPage<ViewMcCashWithdrawApplicationDto>(pageIndex, pageSize, strTableName, null, strSelectColumn2, colOrder, strWhere,
                ref totalCount);
        }

        /// <summary>
        /// 事务增加一条数据
        /// </summary>
        public int TranAdd(SqlConnection connection, SqlTransaction sqlTrans, McCashWithdrawApplication model)
        {
            var strSql = new StringBuilder();
            strSql.Append("insert into McCashWithdrawApplication(");
            strSql.Append("PayTime,PayOptAccountId,Longitude,Remark,Latitude,IpAddress,DealStatus,IsDelete,CashValue,ApplyAccountId,ApplyTime,ShopId,BankAccount,BankName,BankOfDeposit,BankAccountName");
            strSql.Append(") values (");
            strSql.Append("@PayTime,@PayOptAccountId,@Longitude,@Remark,@Latitude,@IpAddress,@DealStatus,@IsDelete,@CashValue,@ApplyAccountId,@ApplyTime,@ShopId,@BankAccount,@BankName,@BankOfDeposit,@BankAccountName");
            strSql.Append(") ");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
			            new SqlParameter("@PayTime", SqlDbType.DateTime) ,            
                        new SqlParameter("@PayOptAccountId", SqlDbType.Int,4) ,            
                        new SqlParameter("@Longitude", SqlDbType.NVarChar,16) ,            
                        new SqlParameter("@Remark", SqlDbType.NVarChar,500) ,            
                        new SqlParameter("@Latitude", SqlDbType.NVarChar,16) ,            
                        new SqlParameter("@IpAddress", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@DealStatus", SqlDbType.Int,4) ,            
                        new SqlParameter("@IsDelete", SqlDbType.Bit,1) ,            
                        new SqlParameter("@CashValue", SqlDbType.Int,4) ,            
                        new SqlParameter("@ApplyAccountId", SqlDbType.Int,4) ,            
                        new SqlParameter("@ApplyTime", SqlDbType.DateTime) ,            
                        new SqlParameter("@ShopId", SqlDbType.Int,4) ,            
                        new SqlParameter("@BankAccount", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@BankName", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@BankOfDeposit", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@BankAccountName", SqlDbType.NVarChar,50)             
              
            };

            parameters[0].Value = model.PayTime;
            parameters[1].Value = model.PayOptAccountId;
            parameters[2].Value = model.Longitude;
            parameters[3].Value = model.Remark;
            parameters[4].Value = model.Latitude;
            parameters[5].Value = model.IpAddress;
            parameters[6].Value = model.DealStatus;
            parameters[7].Value = model.IsDelete;
            parameters[8].Value = model.CashValue;
            parameters[9].Value = model.ApplyAccountId;
            parameters[10].Value = model.ApplyTime;
            parameters[11].Value = model.ShopId;
            parameters[12].Value = model.BankAccount;
            parameters[13].Value = model.BankName;
            parameters[14].Value = model.BankOfDeposit;
            parameters[15].Value = model.BankAccountName;
            var obj = DbHelperSQL.GetSingle(connection, sqlTrans, strSql.ToString(), parameters);
            return obj == null ? 0 : Convert.ToInt32(obj);
        }
		#endregion
	}
}
